*******************************************************************************
*set working directory
global folder ".."


cd $folder\temp


*******************************************************************************
************** PART 1: Interest Swaps*************************** 
*******************************************************************************
import excel using $folder\data\Swap_CDS_Bloomberg.xlsx, firstrow clear sheet(Swaps)
keep USSWAP10BGNCurrencyIndex T
rename USSWAP10BGNCurrencyIndex daten
format daten %td
rename T swap_rate_10y
tsset daten
tsline swap_rate_10y
save swap_rate_10y, replace


import excel using $folder\data\Swap_CDS_Bloomberg.xlsx, firstrow clear sheet(Swaps)
keep USSWAP20BGNCurrencyIndex Z
rename USSWAP20BGNCurrencyIndex daten
format daten %td
rename Z swap_rate_20y
tsset daten
tsline swap_rate_20y
duplicates drop daten, force
save swap_rate_20y, replace

import excel using $folder\data\Swap_CDS_Bloomberg.xlsx, firstrow clear sheet(Swaps)
keep  USSWAP30BGNCurrencyIndex AB
rename USSWAP30BGNCurrencyIndex daten
format daten %td
rename AB swap_rate_30y
tsset daten
tsline swap_rate_30y
duplicates drop daten, force
save swap_rate_30y, replace

import excel using $folder\data\Swap_CDS_Bloomberg.xlsx, firstrow clear sheet(Treasuries)
keep G7O2 J
rename G7O2 daten
format daten %td
rename J t_yield_10y
tsset daten
tsline t_yield_10y
save t_yield_10y, replace

import excel using $folder\data\CDS.xlsx, firstrow clear
rename Date daten
foreach var of varlist USA* {
replace `var'=`var'/100
}
drop if missing(daten)
save usa_cds, replace
/*
import excel using $folder\data\USA_CDS.xlsx, firstrow clear
rename Code daten
rename USV usa_cds
replace usa_cds=usa_cds/100
drop if missing(daten)
save usa_cds, replace
*/
capture: erase "__000000.txt"
freduse  DGS10 DGS20 DGS30 VXTYN VIXCLS, clear
format daten %td
merge 1:1 daten using swap_rate_10y
drop _m
merge 1:1 daten using swap_rate_20y
drop _m
merge 1:1 daten using swap_rate_30y
drop _m
merge 1:1 daten using usa_cds
drop _m


foreach num in 10 20 30 {
g spread_`num'y=swap_rate_`num'y-DGS`num'
g discount_`num'y=swap_rate_`num'y-DGS`num'+USAGOVERNMENTSNRCR`num'YE
}
/*
g default_rate = ln(1-usa_cds/100)/(-5)

foreach num in 10 20 30 {
g usa_cds_`num'y=(1-exp(-`num'*default_rate))*100
g spread_`num'y=swap_rate_`num'y-DGS`num'
g discount_`num'y=swap_rate_`num'y-DGS`num'+usa_cds
}
*/
tsset daten
save treasury_swap, replace



*******************************************************************************
************** PART 2: CDS*************************** 
*******************************************************************************
import excel using $folder\data\Swap_CDS_Bloomberg.xlsx, firstrow clear sheet(CDX)
keep CDXIGCDSIGEN5YCorp PX_LAST
rename CDXIGCDSIGEN5YCorp daten
format daten %td
rename PX_LAST CDX_IG
tsset daten
replace CDX_IG=CDX_IG/100
tsline CDX_IG
duplicates drop daten, force
save CDX_IG, replace


import excel using $folder\data\Swap_CDS_Bloomberg.xlsx, firstrow clear sheet(CDX)
keep CDXHYCDSIGEN5YSPRDCorp H
rename CDXHYCDSIGEN5YSPRDCorp daten
format daten %td
rename H CDX_HY
tsset daten
replace CDX_HY=CDX_HY/100
tsline CDX_HY
duplicates drop daten, force
save CDX_HY, replace

use $folder\data\markitcdx\cdxuscorpclean, clear // from wrds
g daten = date(date,"YMD",.)
format daten %td
replace compspr=compspr*100
replace modspr=modspr*100
keep id daten compspr modspr
reshape wide compspr modspr, i(daten) j(id) string
duplicates drop daten, force
save CDX, replace


capture: erase  __000000.txt
freduse  BAMLH0A0HYM2 BAMLC0A4CBBB BAMLC0A1CAAA, clear
tsset daten

merge 1:1 daten using CDX_IG
drop _m

merge 1:1 daten using CDX_HY
drop _m

merge 1:1 daten using CDX
drop _m

tsline BAMLC0A4CBBB CDX_IG modsprIG
tsline BAMLH0A0HYM2  modsprHY

g CDS_basis_HY= CDX_HY-BAMLH0A0HYM2
g CDS_basis_IG= CDX_IG-BAMLC0A4CBBB

g CDS_basis_HY_new= modsprHY-BAMLH0A0HYM2
g CDS_basis_IG_new= modsprIG-BAMLC0A4CBBB

merge 1:1 daten using treasury_swap
drop _m
save covid_asset_prices, replace





*******************************************************************************
************** PART 2: Graphs*************************** 
*******************************************************************************

use covid_asset_prices, clear

tsline CDS_basis_IG CDS_basis_HY if daten>=td(01jan2020) & daten<=td(30apr2020), ///
lpattern(solid dash) graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	xtitle("") ytitle("CDS-bond basis (%)")  legend(label(1 "IG") label(2 "HY"))  ///	
	ylabel(, nogrid) xlabel(,format(%tdm))   tline(15mar2020, lpattern(dot))  tline(23mar2020, lpatter(dot)) tline(9apr2020, lpatter(dot)) 
graph export  "$folder\output\CDS_basis.pdf", replace



tsline spread*  if daten>=td(01jan2020) & daten<=td(30apr2020), graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	xtitle("") ytitle("US Treasury Swap Spread ")  legend(label(1 "10 year") label(2 "20 year") label(3 "30 year")) ///	
	ylabel(-0.8(.2).4, nogrid) xlabel(,format(%tdmCY)) tline(15mar2020, lpattern(dot))  tline(23mar2020, lpatter(dot)) tline(9apr2020, lpatter(dot)) 
graph export  "$folder\output\US_Swap_Spread.pdf", replace


tsline spread*  if daten>=td(01july2008) & daten<=td(30oct2008), graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	xtitle("") ytitle("US Treasury Swap Spread ")  legend(label(1 "10 year") label(2 "20 year") label(3 "30 year")) ///	
	ylabel(-0.8(.2).4, nogrid) xlabel(,format(%tdmY)) 
graph export  "$folder\output\US_Swap_Spread_2008.pdf", replace


tsline VXTYN if daten>=td(01jan2020) & daten<=td(30apr2020), graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	 legend(off ) ///
	xtitle("") ytitle("CBOE 10-Year Treasury Note Volatility")  legend(label(1 "Treasury  Volatility")) ///	
	ylabel(0(5)20, nogrid) xlabel(,format(%tdm)) tline(15mar2020, lpattern(dot))  tline(23mar2020, lpatter(dot)) tline(9apr2020, lpatter(dot)) 
graph export  "$folder\output\treasury_vol.pdf", replace

tsline VXTYN  if daten>=td(01july2008) & daten<=td(30oct2008), graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	 legend(off ) ///
	xtitle("") ytitle("CBOE 10-Year Treasury Note Volatility")  legend(label(1 "Treasury  Volatility")) ///	
	ylabel(0(5)20, nogrid) xlabel(,format(%tdmY)) 
graph export  "$folder\output\treasury_vol_2008.pdf", replace

tsline discount_*  if daten>=td(01jan2020) & daten<=td(30apr2020), graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	xtitle("") ytitle("US Treasury Discount")  legend(label(1 "10 year") label(2 "20 year") label(3 "30 year")) ///	
	ylabel(-0.6(.2).6, nogrid) xlabel(,format(%tdm))  tline(15mar2020, lpattern(dot))  tline(23mar2020, lpatter(dot)) tline(9apr2020, lpatter(dot)) 
graph export  "$folder\output\US_Swap_Spread_CDS_adjusted.pdf", replace


tsline discount_*  if daten>=td(01july2008) & daten<=td(30oct2008), graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	xtitle("") ytitle("US Treasury Discount")  legend(label(1 "10 year") label(2 "20 year") label(3 "30 year")) ///	
	ylabel(-0.6(.2)1, nogrid) xlabel(,format(%tdmCY)) 
graph export  "$folder\output\US_Swap_Spread_2008_CDS_adjusted.pdf", replace




tsline discount_*  if daten>=td(01july2008) & daten<=td(30oct2008), graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	xtitle("") ytitle("US Treasury Discount")  legend(label(1 "10 year") label(2 "20 year") label(3 "30 year")) ///	
	ylabel(-0.6(.2)1, nogrid) xlabel(,format(%tdmCY)) 
graph export  "$folder\output\US_Swap_Spread_2008_CDS_adjusted.pdf", replace

tsline discount_*  if daten>=td(01july2008) & daten<=td(30oct2009), graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	xtitle("") ytitle("US Treasury Discount")  legend(label(1 "10 year") label(2 "20 year") label(3 "30 year")) ///	
	ylabel(-0.6(.2)1, nogrid) xlabel(,format(%tdmCY)) 


	
use covid_asset_prices, clear
	
twoway connect discount_30 daten if daten>=td(01jan2020) & daten<=td(30apr2020), graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
xtitle("") ytitle("US Treasury CDS-adjusted adjusted swap spreads (%)")  legend(label(1 "10 year") label(2 "20 year") label(3 "30 year")) ///	
ylabel(, grid) xlabel(,format(%tdm))  tline(15mar2020, lpattern(dot))  tline(23mar2020, lpatter(dot)) tline(9apr2020, lpatter(dot)) 
graph export  "$folder\output\connect_treasury_discount_30y.pdf", replace


twoway connect VXTYN daten  if daten>=td(01jan2020) & daten<=td(30apr2020), graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
xtitle("") ytitle("Treasury Volatility (%)")  legend(label(1 "10 year") label(2 "20 year") label(3 "30 year")) ///	
ylabel(, grid) xlabel(,format(%tdm))  tline(15mar2020, lpattern(dot))  tline(23mar2020, lpatter(dot)) tline(9apr2020, lpatter(dot)) 
graph export  "$folder\output\connect_treasury_vol.pdf", replace


twoway (connect CDS_basis_IG daten) (connect CDS_basis_HY daten) if daten>=td(01jan2020) & daten<=td(30apr2020), ///
graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	xtitle("") ytitle("CDS-bond basis (%)")  legend(label(1 "IG") label(2 "HY"))  ///	
	ylabel(, grid) xlabel(,format(%tdm))   tline(15mar2020, lpattern(dot))  tline(23mar2020, lpatter(dot)) tline(9apr2020, lpatter(dot)) 
graph export  "$folder\output\connect_CDS_basis.pdf", replace


twoway connect discount_30 daten if daten>=td(04july2008) & daten<=td(04nov2008), graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
xtitle("") ytitle("US Treasury Discount (%)")  legend(label(1 "10 year") label(2 "20 year") label(3 "30 year")) ///	
ylabel(, grid) xlabel(,format(%tdmY))    tline(15sep2008, lpatter(dash))  ttext( 0 15sep2008 "Lehman bankruptcy") 
graph export  "$folder\output\connect_treasury_discount_30y_2008.pdf", replace



twoway connect VXTYN daten  if daten>=td(04july2008) & daten<=td(04nov2008), graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
xtitle("") ytitle("Treasury Volatility (%)")  legend(label(1 "10 year") label(2 "20 year") label(3 "30 year")) ///	
ylabel(, grid) xlabel(,format(%tdmY))   tline(15sep2008, lpatter(dash)) ttext( 14 15sep2008 "Lehman bankruptcy") 
graph export  "$folder\output\connect_treasury_vol_2008.pdf", replace

twoway (connect CDS_basis_IG_new daten) (connect CDS_basis_HY_new daten) if daten>=td(04july2008) & daten<=td(04nov2008), ///
graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	xtitle("") ytitle("CDS-bond basis (%)")  legend(label(1 "IG") label(2 "HY"))  ///	
ylabel(, grid) xlabel(,format(%tdmY))    tline(15sep2008, lpatter(dash))  ttext( -4 15sep2008 "Lehman bankruptcy") 
graph export  "$folder\output\connect_CDS_basis_2008.pdf", replace


	
twoway connect discount_30 daten if daten>=td(01jan2020) & daten<=td(30apr2020), graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
xtitle("") ytitle("US Treasury Discount (%)")  legend(label(1 "10 year") label(2 "20 year") label(3 "30 year")) ///	
ylabel(, grid) xlabel(,format(%tdm))  tline(13mar2020, lpattern(dash))  ttext( .1 13mar2020 "National emergency") 
graph export  "$folder\output\connect_treasury_discount_30y_new.pdf", replace



twoway connect VXTYN daten  if daten>=td(01jan2020) & daten<=td(30apr2020), graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
xtitle("") ytitle("Treasury Volatility (%)")  legend(label(1 "10 year") label(2 "20 year") label(3 "30 year")) ///	
ylabel(, grid) xlabel(,format(%tdm))  tline(13mar2020, lpattern(dash))  ttext( 0 13mar2020 "National emergency") 
graph export  "$folder\output\connect_treasury_vol_new.pdf", replace


twoway (connect CDS_basis_IG_new daten) (connect CDS_basis_HY_new daten) if daten>=td(01jan2020) & daten<=td(30apr2020), ///
graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	xtitle("") ytitle("CDS-bond basis (%)")  legend(label(1 "IG") label(2 "HY"))  ///	
ylabel(, grid) xlabel(,format(%tdm))  tline(13mar2020, lpattern(dash))  ttext( -.5 13mar2020 "National emergency") 
graph export  "$folder\output\connect_CDS_basis_new.pdf", replace


g diff_IG_HY= CDS_basis_IG_new- CDS_basis_HY_new

twoway (connect diff_IG_HY daten) if daten>=td(01jan2020) & daten<=td(30apr2020), ///
graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	xtitle("") ytitle("CDS-bond basis (%)")  legend(off)  ///	
ylabel(, grid) xlabel(,format(%tdmY))  tline(13mar2020, lpattern(dash))  ttext( .5 13mar2020 "National emergency") 
graph export  "$folder\output\connect_diff_IG_HY.pdf", replace


twoway (connect diff_IG_HY daten) if daten>=td(04july2008) & daten<=td(04nov2008), ///
graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	xtitle("") ytitle("CDS-bond basis (%)")  legend(off)  ///	
ylabel(, grid) xlabel(,format(%tdmY))   tline(15sep2008, lpatter(dash)) ttext( 14 15sep2008 "Lehman bankruptcy") 
graph export  "$folder\output\connect_diff_IG_HY_2008.pdf", replace


*g crisis=(daten>=td(05mar2020) & daten<=td(31mar2020))


g label = substr(date,6,10)

twoway (scatter  DGS30 VIXCLS if daten>=td(01jan2020) & daten<=td(29feb2020),mlabel(label))  ///
(scatter  DGS30 VIXCLS if daten>=td(01mar2020) & daten<=td(30apr2020),mlabel(label)  msymbol(triangle hollow)) , ///
graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	xtitle("VIX") ytitle("30-year Treasury yield (%)")  legend(off )  ///	
ylabel(, nogrid) 
graph export  "$folder\output\scatter_Treasury_VIX_JanApr_2020.pdf", replace



twoway (scatter  DGS30 VIXCLS,mlabel(label)) if daten>=td(01mar2020) & daten<=td(30apr2020), ///
graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	xtitle("VIX") ytitle("30-year Treasury yield (%)")  legend(off )  ///	
ylabel(, nogrid) 
graph export  "$folder\output\scatter_Treasury_VIX_MarApr_2020.pdf", replace

twoway (scatter  DGS30 VIXCLS ,mlabel(label)) if daten>=td(01aug2008) & daten<=td(30sep2008), ///
graphregion(color(white)) plotregion(color(white)) bgcolor(none) ///
	xtitle("VIX") ytitle("30-year Treasury yield (%)")  legend(off)  ///	
ylabel(, nogrid) 
graph export  "$folder\output\scatter_Treasury_VIX_AugSep_2008.pdf", replace



	 
